#!/usr/bin/env python
#-*-coding: utf-8 -*-

import sys
import xlrd
import operacoes_bd
from unidecode import unidecode                     #retirado de http://pypi.python.org/pypi/Unidecode

##
###############################################################################
##
# metodo que verifica o ficheiro fornecido tem a extenção correcta
# tenta abrir o ficheiro e se conseguir tenta converter o 
#conteudo para UTF-8
def abrir_ler_dados( nome_ficheiro, nome_bd):
    '''
    Abrir um ficheiro do excel utilizando o 
    modulo de leitura de ficheiros excel em python, xlrd
        nome_ficheiro - nome do ficheiro a abrir (caminho relativo ou absoluto )
        nome_bd - nome e/ou endereço do ficheiro base de dados one guardar
    '''        
    livro = None
    if (not (nome_ficheiro[-4:] == '.xls')):
        print 'WRONG FILE GIVEN!'
        return
    else:
        try:
            livro = xlrd.open_workbook(nome_ficheiro, formatting_info=True)
        except :
            print "ERROR OPENING '{0}'".format(nome_ficheiro)
            return
        else:
            conn = operacoes_bd.sql_ligar_a_bd(nome_bd)
            cursor = conn.cursor()
            dic_dados = {}
            keys = livro.sheet_names()
                
            for npag in range(2, livro.nsheets):                
                
                pag_dados = livro.sheet_by_index(npag)
##                atrib = obter_colunas2(pag_dados)
                
                atrib = obter_campos_tabela(pag_dados)
                
                atrib = unidecoding(atrib)
                operacoes_bd.sql_criar_tabelas(cursor, atrib[0], atrib[1:])
                conn.commit()
                sql_stmt = criar_sql_querys_1(atrib[0], atrib[1:])
                
                pag_dados = obter_campos2(pag_dados)
                
                unidecoding(pag_dados)
                operacoes_bd.sql_preencher_tabelas1(cursor, pag_dados, sql_stmt)
                conn.commit()
                print 'TTTTTTTTTTTEEERRRRMMMIINNNOOOUUUU'
            pass            
            operacoes_bd.sql_guardar_desligar_bd(conn, cursor)
        pass
    pass
pass

##
###############################################################################
##
#
def criar_sql_querys_1(nome_t, nome_colunas):
    '''
    recebe um dicionario com cheve de nome da 
    tabela e os campos como valo e formula apartir 
    de cada campo do dicionario uma expressao sql de criacao de tabelas
    '''
    sql_v = ' VALUES('
    v = '?'
    nm_coluna = []
    for campo1 in nome_colunas:
        nm_coluna.append(campo1[0])
        sql_v += '?,'
    pass
    colunas = tuple(nm_coluna)
    sql_i = 'INSERT INTO {0} {1}'.format(nome_t, colunas)
    
    sql_v = sql_v[:sql_v.rfind(',')]
    sql_v += ')'
        
    sql_i = sql_i + (sql_v)
    return sql_i
    #preenchimento com os valores do dic_dados    
pass

##
###############################################################################
##
def obter_campos2(pagina, dl = False):

##    print pagina.merged_cells
    #obter e redistribuir os dados das celulas merged
    lista_m = {}
    for crange in pagina.merged_cells:
        rlo, rhi, clo, chi = crange
        for rowx in xrange(rlo, rhi):
            for colx in xrange(clo, chi):               
                if ((pagina.cell_value(rlo, clo) == 'Total') or (pagina.cell_value(rlo, clo) == 'total')):
                    continue
                if ((pagina.cell_value(rlo, clo) == 'TOTAL') or (pagina.cell_value(rlo, clo) == 'PORTUGAL')): 
                    break
                else:
                    lista_m[(rowx, colx)] = pagina.cell_value(rlo, clo)
                pass
            pass
            if ((pagina.cell_value(rlo, clo) == 'TOTAL') or (pagina.cell_value(rlo, clo) == 'PORTUGAL')): 
                break
        pass
    pass
        
    lista_D = []                                                #lista definitiva
    
    cel_actual = ''
    cel_ant = ''
    dd1 = ['' for k in range(pagina.ncols)]
    p = 0
    if (pagina.cell_value(3,0) == ''):                                      # cabeçalhos com formatação multilinha ou
        i = 4
    else:
        i = 3                                                               # cabeçalho valor por linha
    pass
    
    #obter os restantes dos ddados das tabelas
    for ln in xrange(i, pagina.nrows):
        dados1 = []                                             #lista dos campos de uma linha
        col_ant = 'anos'
        for cl in xrange(pagina.ncols):
            cell_v = (ln, cl)
            
            cel_actual = pagina.cell_value(ln, cl)
            
            if ((2, cl) in lista_m.keys()):
                v_ano = lista_m[(2, cl)]
            else:
                v_ano = pagina.cell_value(2, cl)
            pass
            
            if (v_ano != ''):                
                if (cell_v in lista_m.keys()):
                    cvalue = lista_m[cell_v]
                else:
                    cvalue = pagina.cell_value(ln, cl)
                pass            
                
                if ((cvalue == 'Total') or 
                    (cvalue == 'total') or (cvalue == 'HM')):
                    break                                           #nao ler as linhas com valores totais
                if ((cvalue == 'TOTAL') or (cvalue == 'PORTUGAL')): 
                    break                                           #indicação que chegou ao fim da tabela dos dados
                else:                    
                    
                    if (v_ano[:3].isdigit() and ((cvalue == '') or (cvalue == '-'))): 
                        cvalue = 0.0
                    cnt = 0
                    if (isinstance(cvalue, float)):

                        if (col_ant[:3].isdigit()):                         #se se trata do valor de um ano
                            if (col_ant == v_ano):
                                dados1.append(cvalue)
                            else:
                                dados1.append(col_ant)
                                lista_D.append(tuple(dados1))              #guarda os dados agrupados
                                
                                dados1 = dados1[:cut_p]                    #reiniciar a lista com a mesma idade
                                dados1.append(cvalue)
                                col_ant = v_ano
                            pass
                        else:
                            cut_p = len(dados1)
                            
                            dados1.append(cvalue)
                            col_ant = v_ano
                        pass
                    else:
                        if (cvalue != ''):
                            dados1.append(cvalue)
                            dd1[p] = cvalue
                            p +=1
                        else:
                            cvalue = dd1[p]
                            dados1.append(cvalue)
                            p +=1
                        pass
                    pass
                pass
            pass
        pass
        p = 0
        if ((cvalue == 'TOTAL') or (cvalue == 'PORTUGAL')): 
            break                                    #indicação que chegou ao fim da tabela dos dados
        pass
    pass
    ff.close()
    return lista_D
pass

##
###############################################################################
##
# metodo que retorna uma lista com o nome para a 
# tabela e os respectivos campos numa base de dados
def obter_campos_tabela(f):   
    '''
    recebe  uma pagina de dados excel 
    e retorna uma lista com o nome e os valores do cabeçalho 
    no formato (campo, tipo) pronto para operações sobre a base de dados
    
    f - folha de onde extrair os dados
    return 'colunas' - list object
    '''                

    colunas = []
    colunas.append(f.name)                                              # formatano  
    colunas[0] = colunas[0].replace(' ', '_',1)                         # o nome
    colunas[0] = colunas[0].replace('.', '_',1)                         # da tabela
    colunas[0] = colunas[0].replace(' ', '',1)
    
    tipo_texto = 'VARCHAR(50)'                                          # tipo dos campos textos/strings
    tipo_num = 'CHAR(10)'                                               # tipos dos campos numericos

    for ln in range(2,4):
        val_linha = f.row_values(ln)
        
        for pos in xrange(len(val_linha)):                              # por cada coluna numa linha
            valor = val_linha[pos]
            v_ant = val_linha[pos -1]
            if (not (valor[:3].isdigit()) and (valor != '')):           # se nao for valor das colunas de anos
                valor = valor.strip()
                valor = valor.replace(' ', '_')                         # formata substituindo os espaços
                if (ln == 3):
                    valor = (valor, tipo_num)                           # adiciona o tipo numerico ou
                else:
                    valor = (valor, tipo_texto)                         #ou tipo texto
                pass
                if (valor not in colunas):
                    colunas.append(valor)                               # se aiinda nao existir na lista, adiciona
                pass
            elif(valor == ''):                                          # se encontrar coluna com valor mas sem cabeçalho
                tmp_v = v_ant[:v_ant.find(' ')]
                if (tmp_v == 'Subsistema'):                             # case de colunas merged mas que nao aparecemm 
                    valor = 'Tipo_' + v_ant[v_ant.rfind(' ')+1:]        # nas tabelas de merged_cell (bug?)
                    colunas.append((valor, tipo_texto))
                pass
            pass
        if (f.row(2+1)[0].value != ''):                                 # o primeiro valor da linha 3
            colunas.append(('Valor', tipo_num))
            break
        pass
    pass
    colunas.append(('Ano', tipo_texto))                                 # por fim adiciona o campo 'Ano'
    return colunas                                                      # e retorna uma lista
pass





##
###############################################################################
##
#codificando os dados fornecidos para unicode
def unidecoding(lista):
    '''recebe uma lista de tuplos e converte os campos strings 
        para unicode retirando as acentuações das palavras
        
        lista - conjunto de dados a converter
        return 'n_lista' - nova lista com os dados convertidos
    '''
    n_lista = []
    n_lista.append(lista[0])
    lista = lista[1:]
    for pos in xrange(len(lista)):
        tmp_lst = []
        for cmp in lista[pos]:
            
            if (isinstance(cmp, unicode)):
                tmp_lst.append(unidecode(cmp))
            else:
                tmp_lst.append(cmp)
            pass
        pass
        n_lista.append(tuple(tmp_lst))
    pass
    return n_lista
pass

##
###############################################################################
##
if __name__ == "__main__":
    def usage():
        text = """
        usage:
            no ambiente python:
                lp1213-4708.py nF.xls cN_bd[.ext]
            
            nF.xls -> nome do ficheiro no formato excel
            cN_bd[.ext] -> nome ou caminho (com ou sem extensão)
                        do ficheiro para base de dados \n\n"""
        
        sys.stdout.write(text)
    pass
    
    if len(sys.argv) < 3:
        usage()
        sys.exit(0)
    if len(sys.argv) == 3:
        print
##        abrir_ler_dados(sys.argv[1], sys.argv[2])
        
        abrir_ler_dados(sys.argv[1], sys.argv[2])
    pass